package exttable.timetable;

import dbutils.DbUtils;
import dbutils.StringQueries;
import exttable.SGTTestParametrs;
import exttable.timetable.models.AnalysisModel;
import exttable.timetable.models.VisitModel;
import exttable.timetable.xlsgenerator.XLSGenerator;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by IntelliJ IDEA.
 * User: Admin
 * Date: Apr 30, 2010
 * Time: 12:22:58 AM
 * To change this template use File | Settings | File Templates.
 */
public class TimeTableSaver {

    public static final int DAYS_TO_SECONDS = 86400;
    public static final int HOURS_TO_SECONDS = 3600;
    public static final int MINUETS_TO_SECONDS = 60;
    Connection con = null;

    public TimeTableSaver() {
        try {
            con = DbUtils.getDBConnectionGeneral();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean saveInDB(Map<ETestType, List<AnalysisModel>> data, SGTTestParametrs ourInstance) {
        try {
            int schemaId = createNewSchema(ourInstance);
            return saveVisitsGrid(data, schemaId);
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    private int createNewSchema(SGTTestParametrs ourInstance) throws Exception {
        int schemaId = 0;
        ResultSet rs = con.prepareStatement(StringQueries.MAX_SCHEMA_VISIT).executeQuery();
        while (rs.next()) {
            schemaId = rs.getInt(1);
        }
        schemaId += 1;
        String saveSHquery = "INSERT INTO schema_visits(schema_visits_id, name_ru, name_en, name_kk) VALUES(?, ?, ?, ?)";
        PreparedStatement ps = con.prepareStatement(saveSHquery);
        ps.setInt(1, schemaId);
        ps.setString(2, ourInstance.getSchemaDescRu());
        ps.setString(3, ourInstance.getSchemaDescEn());
        ps.setString(4, ourInstance.getSchemaDescKk());
        ps.executeUpdate();
        ps.close();
        return schemaId;
    }

    private boolean saveVisitsGrid(Map<ETestType, List<AnalysisModel>> data, int schemaId) throws Exception {
        try {
            Set<ETestType> set = data.keySet();
            Iterator<ETestType> it = set.iterator();
            ETestType et = it.next();

            String saveVisitsQuery = "INSERT INTO schema_visit_time(schema_visits_id, visit_id, time_mls) VALUES(?, ?, ?)";
            PreparedStatement ps = con.prepareStatement(saveVisitsQuery);

            int visid = 1;
            for (VisitModel res : data.get(et).get(0).getVisitModel()) {
                ps.setInt(1, schemaId);
                ps.setInt(2, visid++);
                ps.setInt(3, getMillSec(res.getDays(), DAYS_TO_SECONDS) + getMillSec(res.getHours(), HOURS_TO_SECONDS) + getMillSec(res.getMinuets(), MINUETS_TO_SECONDS));
                ps.addBatch();
            }
            int[] i = ps.executeBatch();
            ps.close();
            if (i.length > 0) {
                return saveTimeTable(schemaId, data);
            }
            return i.length > 0;
        }
        catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }

    }

    private boolean saveTimeTable(int schemaId, Map<ETestType, List<AnalysisModel>> data) throws Exception {
        try {
            int visit = 1;
            for (Map.Entry<ETestType, List<AnalysisModel>> res : data.entrySet()) {
//                System.out.println("GR: " + res.getKey());
                String saveGroupSchemaVisits = "INSERT INTO groups_schema_visits(groups_schema_visits_id, schema_visits_id, dic_test_type_id) VALUES (null, ?, ?)";
                PreparedStatement ps = con.prepareStatement(saveGroupSchemaVisits);
                ps.setInt(1, schemaId);
                ps.setString(2, res.getKey().name());
                ps.executeUpdate();

                int gsvid = getGroupSchemaVisitId(schemaId, res.getKey());
                for (AnalysisModel am : res.getValue()) {
//                    System.out.println("name: " + am.getAnalysis().getAnalysisId());
//                    System.out.println("  id: " + am.getAnalysis().getAnalysisVersionKey());
                    for (VisitModel vm : am.getVisitModel()) {
//                        System.out.println("   visit: " + visit);
//                        System.out.println("   carried: " + vm.isCarried());
                        String saveTimeTableRecord = "INSERT INTO timetable(id, groups_schema_visits_id, analysis_version_key, visit_id, is_carried_out) VALUES (null, ?, ?, ?, ?)";
                        ps = con.prepareStatement(saveTimeTableRecord);
                        ps.setInt(1, gsvid);
                        ps.setString(2, am.getAnalysis().getAnalysisVersionKey());
                        ps.setInt(3, visit);
                        ps.setInt(4, vm.isCarried() ? 1 : 0);
                        ps.executeUpdate();
                        visit++;
                    }
                    visit = 1;
                }
            }

            XLSGenerator.createFile(data);
            con.close();
            return true;
        }
        catch (SQLException e) {
            con.close();
            e.printStackTrace();
            throw e;
        }
        catch (IOException e) {
            con.close();
            e.printStackTrace();
            throw e;
        }
        catch (Exception e) {
            con.close();
            e.printStackTrace();
            throw e;
        }
    }

    private int getGroupSchemaVisitId(int schId, ETestType tType) throws Exception {
        String retriveSGVid = "select groups_schema_visits_id from groups_schema_visits where schema_visits_id = ? and dic_test_type_id = ?";
        PreparedStatement ps = con.prepareStatement(retriveSGVid);
        ps.setInt(1, schId);
        ps.setString(2, tType.name());
        ResultSet rs = ps.executeQuery();
        int toReturn = 0;
        while (rs.next()) {
            toReturn = rs.getInt("groups_schema_visits_id");
        }
        return toReturn;
    }

    private int getMillSec(int period, int product) {
        return period * product;
    }


}
